package com.elight.utils;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.elight.utils.MapUtils;

public class DbUtils {
	/**
	 * 获得连接
	 * 
	 * @return
	 */
	public Connection getConn() {
		Connection conn = null;
		try {
			Class.forName(PropertyPlaceholder.getProperty("jdbc.driverClassName").toString());
			conn = DriverManager.getConnection(PropertyPlaceholder.getProperty("jdbc.url").toString(),
					PropertyPlaceholder.getProperty("jdbc.username").toString(),
					PropertyPlaceholder.getProperty("jdbc.password").toString());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 获得预处理
	 * 
	 * @param conn
	 * @param sql
	 * @return
	 */
	public PreparedStatement prepare(Connection conn, String sql) {
		try {
			return conn.prepareStatement(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 关闭预处理
	 * 
	 * @param ps
	 */
	public void close(PreparedStatement ps) {
		try {
			if (null != ps) {
				ps.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void close(Connection conn) {
		try {
			if (null != conn) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 执行一条sql
	 * 
	 * @param sql
	 * @param obj
	 * @return
	 */
	public boolean execute(String sql, Object[] obj) {
		int n = 0;
		Connection conn = getConn();
		PreparedStatement ps = prepare(conn, sql);
		try {
			addSqlParameter(ps, obj);
			n = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(ps);
			close(conn);
		}
		if (n > 0)
			return true;
		return false;
	}

	/**
	 * 执行一条select语句返回一张数据表，支持多表查询
	 */
	public List<Map<String, Object>> select(StringBuilder strSql, Object[] obj) {
		return select(strSql.toString(), obj);
	}

	/**
	 * 执行多条sql,使用事务
	 * 
	 * @param sql
	 * @return
	 */
	public boolean execute(StringBuilder... sql) {
		Connection conn = getConn();
		PreparedStatement ps = null;
		try {
			conn.setAutoCommit(false);
			conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
			for (StringBuilder strSql : sql) {
				ps = prepare(conn, strSql.toString());
				ps.executeUpdate();
			}
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
				return false;
			} catch (SQLException e1) {
				return false;
			}
		} finally {
			close(ps);
			close(conn);
		}
		return true;
	}

	/**
	 * 执行多条sql,使用事务
	 * 
	 * @param sql
	 * @return
	 */
	public boolean execute(String sql, List<Object[]> list) {
		Connection conn = getConn();
		PreparedStatement ps = null;
		try {
			conn.setAutoCommit(false);
			conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
			for (Object[] obj : list) {
				ps = prepare(conn, sql);
				addSqlParameter(ps, obj);
				ps.executeUpdate();
				ps = null;
			}
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
				return false;
			} catch (SQLException e1) {
				return false;
			}
		} finally {
			close(ps);
			close(conn);
		}
		return true;
	}

	/**
	 * 执行多条sql,使用事务
	 * 
	 * @param sql
	 * @return
	 */
	public boolean execute(String... sql) {
		Connection conn = getConn();
		PreparedStatement ps = null;
		try {
			conn.setAutoCommit(false);
			conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
			for (String strSql : sql) {
				ps = prepare(conn, strSql);
				ps.executeUpdate();
			}
			conn.commit();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			try {
				conn.rollback();
				return false;
			} catch (SQLException e1) {
				return false;
			}
		} finally {
			close(ps);
			close(conn);
		}
		return true;
	}

	/**
	 * 执行一条select语句返回一个对象集合，支持多表查询
	 * 
	 * @param sql
	 * @param obj
	 * @param clazz
	 * @return
	 */
	public <T> List<T> select(StringBuilder sql, Object[] obj, Class<T> clazz) {
		List<T> list = new ArrayList<T>();
		List<Map<String, Object>> list2 = select(sql, obj);
		for (Map<String, Object> map : list2) {
			list.add(MapUtils.mapToBean(map, clazz));
		}
		return list;
	}

	/**
	 * 执行一条select语句返回一个对象集合，支持多表查询
	 * 
	 * @param sql
	 * @param obj
	 * @param clazz
	 * @return
	 */
	public <T> List<T> select(String sql, Object[] obj, Class<T> clazz) {
		List<T> list = new ArrayList<T>();
		List<Map<String, Object>> list2 = select(sql, obj);
		for (Map<String, Object> map : list2) {
			list.add(MapUtils.mapToBean(map, clazz));
		}
		return list;
	}

	/**
	 * 执行一条select语句返回一张数据表，支持多表查询
	 */
	public List<Map<String, Object>> select(String sql, Object[] obj) {
		boolean flag = false; // 是否有数据
		Connection conn = getConn();
		PreparedStatement ps = prepare(conn, sql);
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			addSqlParameter(ps, obj);
			ResultSet rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			// 此处开始循环读数据，每次往表格中插入一行记录
			while (rs.next()) {
				flag = true;
				Map<String, Object> map = new HashMap<String, Object>();
				// 此处开始列循环，每次向一行对象插入一列
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					String columnName = rsmd.getColumnName(i);
					Object value = rs.getObject(columnName);
					// 初始化单元列
					map.put(columnName, value);
				}
				list.add(map);
			}
			rs.close();
			rs = null;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(ps);
			close(conn);
		}
		if (!flag) {
			return null;
		}
		return list;
	}

	/**
	 * 执行一条select语句返回一条记录，支持多表查询
	 * 
	 * @param strSql
	 * @param obj
	 * @return
	 */
	public Map<String, Object> selectOneResult(StringBuilder strSql, Object[] obj) {
		return selectOneResult(strSql.toString(), obj);
	}

	/**
	 * 执行一条select语句返回一条记录对象，支持多表查询
	 * 
	 * @param sql
	 * @param obj
	 * @param clazz
	 * @return
	 */
	public <T> T selectOneResult(String sql, Object[] obj, Class<T> clazz) {
		Map<String, Object> map = selectOneResult(sql, obj);
		if (map == null)
			return null;
		return MapUtils.mapToBean(selectOneResult(sql, obj), clazz);
	}

	/**
	 * 执行一条select语句返回一条记录对象，支持多表查询
	 * 
	 * @param sql
	 * @param obj
	 * @param clazz
	 * @return
	 */
	public <T> T selectOneResult(StringBuilder sql, Object[] obj, Class<T> clazz) {
		Map<String, Object> map = selectOneResult(sql, obj);
		if (map == null)
			return null;
		return MapUtils.mapToBean(map, clazz);
	}

	/**
	 * 执行一条select语句返回一条记录，支持多表查询
	 * 
	 * @param sql
	 * @param obj
	 * @return
	 */
	public Map<String, Object> selectOneResult(String sql, Object[] obj) {
		boolean flag = false; // 是否有数据
		Connection conn = getConn();
		PreparedStatement ps = prepare(conn, sql);
		Map<String, Object> map = null;
		try {
			map = new HashMap<String, Object>();
			addSqlParameter(ps, obj);
			ResultSet rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			// 此处开始循环读数据，每次往表格中插入一行记录
			if (rs.next()) {
				flag = true;
				// 此处开始列循环，每次向一行对象插入一列
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					String columnName = rsmd.getColumnLabel(i);
					Object value = rs.getObject(columnName);
					// 初始化单元列
					map.put(columnName, value);
				}
			}
			rs.close();
			rs = null;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(ps);
			close(conn);
		}
		if (!flag) {
			return null;
		}
		return map;
	}

	/**
	 * 增加参数方法
	 */
	private void addSqlParameter(PreparedStatement ps, Object[] obj) throws SQLException {
		if (null == obj)
			return;
		for (int j = 0; j < obj.length; j++) {
			if (obj[j] instanceof String) {
				ps.setString(j + 1, (String) obj[j]);
			}
			if (obj[j] instanceof Integer) {
				ps.setInt(j + 1, (Integer) obj[j]);
			}
			if (obj[j] instanceof Boolean) {
				ps.setBoolean(j + 1, (Boolean) obj[j]);
			}
			if (obj[j] instanceof Date) {
				ps.setDate(j + 1, (Date) obj[j]);
			}
			if (obj[j] instanceof Blob) {
				ps.setBlob(j + 1, (Blob) obj[j]);
			}
			if (obj[j] instanceof Double) {
				ps.setDouble(j + 1, (Double) obj[j]);
			}
			if (obj[j] instanceof Boolean) {
				ps.setBoolean(j + 1, (Boolean) obj[j]);
			}
			if (obj[j] instanceof Float) {
				ps.setFloat(j + 1, (Float) obj[j]);
			}
			if (obj[j] instanceof Long) {
				ps.setLong(j + 1, (Long) obj[j]);
			}
		}
	}
}
